import pymysql
import pandas as pd 

class MysqlUtils(object):
    """数据库工具类
    """

    def  __init__(self):
        self.conn = pymysql.connect(
            host = '127.0.0.1',
            user='root',
            passwd='root',
            db ='scenic',
            port='3306',
            charset='utf8'
        )

    def get_scenic_data(self):
        cursor = self.conn(cursor=pymysql.cursors.DictCursor)
        sql = """
        Select order_id, age, user_count, id_no, user_name, phone FROM (SELECT o.id sd order_id, u.id_no, u.user_name, u.phone,
        case
            when length(u.id_no) = 18 THEN YEAR (now()) -CAST(SUBSTR(u.id_no , 7, 4) as signed)
                else NULL
                end as age,
                (SELECT count(*) FROM ticket_order_user_rel WHERE order_id = o.id) as user_count
        FROM ticket_order o JOIN ticket_order_user_rel u on u.order_id = o.id WHERE u.id_no is not null and u.id_no != ''
        )  as subquery
        WHERE user_count = 1 and (age <18 or age >=60)
        """
            
        cursor.execute(sql)
        ret = cursor.tetchall()
        df = pd.DataFrame(ret)
        print(df.head)
        df.to_csv('./outlier/scenic_data.csv')    

if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()
